﻿Public Class clsProdInfo
    Public Function getProdInfoByProdId(prod_id As String, status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT prod_id,a.prod_spec_id,b.prod_spec_name,steel_seal_no,jie_shou_pq_ope,jie_shou_pq_dt FROM sf.b_prod_info a "
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id "
        szSql &= "WHERE a.factory_id = ? and a.prod_id = ? and a.status = ? and jie_shou_pq_ope is not null and jie_shou_pq_dt is not null"
        Return DBacs.ExecuteSql(szSql, -1, "TS", prod_id, status)
    End Function

    Public Function getProdInfoListJS(startTime As String, endTime As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT prod_id,a.prod_spec_id,b.prod_spec_name,steel_seal_no,jie_shou_pq_ope,jie_shou_pq_dt FROM sf.b_prod_info a "
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id "
        szSql &= "WHERE a.factory_id = ? "
        If startTime <> "" Then
            szSql &= " and a.jie_shou_pq_dt >= '" + startTime + "'"
        End If
        If endTime <> "" Then
            szSql &= " and a.jie_shou_pq_dt <= '" + endTime + "'"
        End If
        Return DBacs.ExecuteSql(szSql, -1, "TS")
    End Function

    Public Function getProdInfoListPQ(startTime As String, endTime As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT prod_id,a.prod_spec_id,b.prod_spec_name,steel_seal_no,pq_ope,pq_dt FROM sf.b_prod_info a "
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id "
        szSql &= "WHERE a.factory_id = ? "
        If startTime <> "" Then
            szSql &= " and a.pq_dt >= '" + startTime + "'"
        End If
        If endTime <> "" Then
            szSql &= " and a.pq_dt <= '" + endTime + "'"
        End If
        Return DBacs.ExecuteSql(szSql, -1, "TS")
    End Function

    Public Function getProdInfoByStatus(status As String, jieshou As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT prod_id,a.prod_spec_id,b.prod_spec_name,steel_seal_no,jie_shou_pq_ope,jie_shou_pq_dt FROM sf.b_prod_info a "
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id "
        szSql &= "WHERE a.factory_id = ? and a.status = ? and jie_shou_pq_ope is not null and jie_shou_pq_dt is not null"
        Return DBacs.ExecuteSql(szSql, -1, "TS", status)
    End Function

    Public Function getProdInfoByStatus(status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT prod_id,a.prod_spec_id,b.prod_spec_name,steel_seal_no,pq_ope,pq_dt FROM sf.b_prod_info a "
        szSql &= " left join sf.t_prod_spec b on a.prod_spec_id = b.prod_spec_id "
        szSql &= "WHERE a.factory_id = ? and a.status = ? "
        Return DBacs.ExecuteSql(szSql, -1, "TS", status)
    End Function

    Public Function getProdInfoByProdIdAndStatus(prod_id As String, status As String) As DataTable
        Dim szSql As String = ""
        szSql &= "SELECT * FROM sf.b_prod_info WHERE factory_id=? and prod_id = ? and status = ? "
        Return DBacs.ExecuteSql(szSql, -1, "TS", prod_id, status)
    End Function

    Public Function updateProdInfo(prod_ids As String, oper As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql &= "update sf.b_prod_info set update_time = (SELECT CURRENT_TIMESTAMP(6)), jie_shou_pq_ope = ?, jie_shou_pq_dt = (SELECT CURRENT_TIMESTAMP(6)) "
            sql &= " where prod_id in ("
            sql &= prod_ids
            sql &= ") "
            DBacs.ExecuteUpdateSql(sql, -1, oper)
            sql = "select prod_id,pgd_id,b.* from sf.b_prod_info a left join sf.b_pgd_nc b on a.pgd_id::integer = b.b_pgd_mes_sys_id and b.procedure_name = ? "
            sql &= " where prod_id in ("
            sql &= prod_ids
            sql &= ") "
            Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, "移库")
            If dt.Rows.Count > 0 Then
                For index = 0 To dt.Rows.Count - 1
                    If String.IsNullOrEmpty(dt.Rows(index)("b_pgd_mes_sys_id").ToString()) = False And String.IsNullOrEmpty(dt.Rows(index)("b_pgd_mes_details_sys_id").ToString()) = False Then
                        sql = "select * from sf.b_pgd_nc_workreport where prod_id = ? and procedure_name = ? "
                        Dim dtws_yk As DataTable = DBacs.ExecuteSql(sql, -1, dt.Rows(index)("prod_id"), "移库")
                        If dtws_yk.Rows.Count = 0 Then
                            sql = "select * from sf.b_pgd_nc_workreport where prod_id = ? and procedure_name = ? "
                            Dim dtws As DataTable = DBacs.ExecuteSql(sql, -1, dt.Rows(index)("prod_id"), "力学")
                            If dtws.Rows.Count > 0 Then
                                sql = "INSERT INTO sf.b_pgd_nc_workreport(b_pgd_mes_sys_id, b_pgd_mes_details_sys_id, oper_date, mes_project_code, mes_project_name, prod_spec_id, prod_spec_name, oper_count, procedure_name,warehouse,is_partial_area,prod_spec_id_partial_area_lx,create_time,prod_id) "
                                sql &= "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6)),?); "
                                If dtws.Rows(0)("is_partial_area").ToString().Equals("2") Then '不偏区
                                    rtn = DBacs.ExecuteUpdateSql(sql, -1, dt.Rows(index)("b_pgd_mes_sys_id").ToString(), dt.Rows(index)("b_pgd_mes_details_sys_id").ToString(), dt.Rows(index)("oper_date"), dt.Rows(index)("mes_project_code"),
                                                         dt.Rows(index)("mes_project_name"), dt.Rows(index)("prod_spec_id"), dt.Rows(index)("prod_spec_name"),
                                                         "1", dt.Rows(index)("procedure_name"), "隔震喷漆库", dtws.Rows(0)("is_partial_area"), dtws.Rows(0)("prod_spec_id"), dt.Rows(index)("prod_id"))
                                End If
                                If dtws.Rows(0)("is_partial_area").ToString().Equals("1") Then '偏区
                                    rtn = DBacs.ExecuteUpdateSql(sql, -1, dt.Rows(index)("b_pgd_mes_sys_id").ToString(), dt.Rows(index)("b_pgd_mes_details_sys_id").ToString(), dt.Rows(index)("oper_date"), dt.Rows(index)("mes_project_code"),
                                                         dt.Rows(index)("mes_project_name"), dt.Rows(index)("prod_spec_id"), dt.Rows(index)("prod_spec_name"),
                                                         "1", dt.Rows(index)("procedure_name"), "隔震喷漆库", dtws.Rows(0)("is_partial_area"), dtws.Rows(0)("prod_spec_id_partial_area_lx"), dt.Rows(index)("prod_id"))
                                End If
                            End If
                        End If
                    End If
                Next
            End If
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function updateProdInfoWR(prod_ids As String, oper As String) As Integer
        Dim sql As String = ""
        Dim rtn As Integer = 0
        DBacs.BeginTransaction()
        Try
            sql &= "update sf.b_prod_info set update_time = (SELECT CURRENT_TIMESTAMP(6)), jie_shou_pq_ope = ?, jie_shou_pq_dt = (SELECT CURRENT_TIMESTAMP(6)), pq_ope = ?, pq_dt = (SELECT CURRENT_TIMESTAMP(6)),status = ? "
            sql &= " where prod_id in ("
            sql &= prod_ids
            sql &= ") "
            DBacs.ExecuteUpdateSql(sql, -1, oper, oper, "14")
            sql = "select prod_id,pgd_id,b.* from sf.b_prod_info a left join sf.b_pgd_nc b on a.pgd_id::integer = b.b_pgd_mes_sys_id and b.procedure_name = ? "
            sql &= " where prod_id in ("
            sql &= prod_ids
            sql &= ") "
            Dim dt As DataTable = DBacs.ExecuteSql(sql, -1, "移库")
            If dt.Rows.Count > 0 Then
                For index = 0 To dt.Rows.Count - 1
                    If String.IsNullOrEmpty(dt.Rows(index)("b_pgd_mes_sys_id").ToString()) = False And String.IsNullOrEmpty(dt.Rows(index)("b_pgd_mes_details_sys_id").ToString()) = False Then
                        sql = "select * from sf.b_pgd_nc_workreport where prod_id = ? and procedure_name = ? "
                        Dim dtws_yk As DataTable = DBacs.ExecuteSql(sql, -1, dt.Rows(index)("prod_id"), "移库")
                        If dtws_yk.Rows.Count = 0 Then
                            sql = "select * from sf.b_pgd_nc_workreport where prod_id = ? and procedure_name = ? "
                            Dim dtws As DataTable = DBacs.ExecuteSql(sql, -1, dt.Rows(index)("prod_id"), "力学")
                            If dtws.Rows.Count > 0 Then
                                sql = "INSERT INTO sf.b_pgd_nc_workreport(b_pgd_mes_sys_id, b_pgd_mes_details_sys_id, oper_date, mes_project_code, mes_project_name, prod_spec_id, prod_spec_name, oper_count, procedure_name,warehouse,is_partial_area,prod_spec_id_partial_area_lx,create_time,prod_id) "
                                sql &= "VALUES ( ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , ? , (SELECT CURRENT_TIMESTAMP(6)),?); "
                                If dtws.Rows(0)("is_partial_area").ToString().Equals("2") Then '不偏区
                                    rtn = DBacs.ExecuteUpdateSql(sql, -1, dt.Rows(index)("b_pgd_mes_sys_id").ToString(), dt.Rows(index)("b_pgd_mes_details_sys_id").ToString(), dt.Rows(index)("oper_date"), dt.Rows(index)("mes_project_code"),
                                                         dt.Rows(index)("mes_project_name"), dt.Rows(index)("prod_spec_id"), dt.Rows(index)("prod_spec_name"),
                                                         "1", dt.Rows(index)("procedure_name"), "隔震喷漆库", dtws.Rows(0)("is_partial_area"), dtws.Rows(0)("prod_spec_id"), dt.Rows(index)("prod_id"))
                                End If
                                If dtws.Rows(0)("is_partial_area").ToString().Equals("1") Then '偏区
                                    rtn = DBacs.ExecuteUpdateSql(sql, -1, dt.Rows(index)("b_pgd_mes_sys_id").ToString(), dt.Rows(index)("b_pgd_mes_details_sys_id").ToString(), dt.Rows(index)("oper_date"), dt.Rows(index)("mes_project_code"),
                                                         dt.Rows(index)("mes_project_name"), dt.Rows(index)("prod_spec_id"), dt.Rows(index)("prod_spec_name"),
                                                         "1", dt.Rows(index)("procedure_name"), "隔震喷漆库", dtws.Rows(0)("is_partial_area"), dtws.Rows(0)("prod_spec_id_partial_area_lx"), dt.Rows(index)("prod_id"))
                                End If
                            End If
                        End If
                    End If
                Next
            End If
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function

    Public Function updateProdInfoPq(prod_ids As String, oper As String) As Integer
        Dim sql As String
        Dim rtn As Integer
        DBacs.BeginTransaction()
        Try
            sql = "update sf.b_prod_info set pq_ope = ?, pq_dt = (SELECT CURRENT_TIMESTAMP(6)), "
            sql &= "status = ?,update_time=(SELECT CURRENT_TIMESTAMP(6))"
            sql &= " where factory_id = ? and prod_id in ("
            sql &= prod_ids
            sql &= ") "
            rtn = DBacs.ExecuteUpdateSql(sql, -1, oper, "14", "TS", prod_ids)
            DBacs.CommitTransaction()
        Catch ex As Exception
            DBacs.RollbackTransaction()
            rtn = -2
        End Try
        Return rtn
    End Function


End Class
